package com.netease.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @projectName: modeling-dev
 * @package: com.netease.utils
 * @className: POIExcelUtils
 * @author: aochong
 * @description: POI操作Excel工具类
 * @date: 2023/4/5 14:19
 * @version: 1.0
 */
public class POIExcelUtils {
    // 总行数
    private int totalRows = 0;
    // 总列数
    private int totalCells = 0;
    private OutputStream out = null;
    private Workbook workbook = null;
    private Sheet sheet = null;
    // 定制浮点数格式
    private Row row = null;
    // 定制日期格式
    private static String NUMBER_FORMAT = "#,##0.00";
    // "m/d/yy h:mm"
    private static String DATE_FORMAT = "m/d/yy";

    public POIExcelUtils() {
    }

    /**
     * @param fileName: 文件名称
     * @param sheetNo: sheet页编号
     * @param beginRowNo: 开始行号
     * @param endRowNo: 结束行号
     * @return List<ArrayList<String>>
     * @author aochong
     * @description 根据文件名读取excel文件
     * @date 2023/4/8 14:23
     */
    public List<ArrayList<String>> read(String fileName, int sheetNo,
                                        int beginRowNo, int endRowNo) {
        List<ArrayList<String>> dataLst = new ArrayList<ArrayList<String>>();
        // 检查文件名是否为空或者是否是Excel格式的文件
        if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            return dataLst;
        }
        // 对文件的合法性进行验
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }               // 检查文件是否存在
        File file = new File(fileName);
        if (file == null || !file.exists()) {
            return dataLst;
        }
        try {
            // 读取excel
            dataLst = read(new FileInputStream(file), isExcel2003, sheetNo,
                    beginRowNo, endRowNo);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return dataLst;
    }

    /**
     * @param inputStream:
     * @param isExcel2003: 是否是2003版本excel
     * @param sheetNo: sheet页索引
     * @param beginRowNo: 开始行号
     * @param endRowNo: 结束行号
     * @return List<ArrayList<String>>
     * @author aochong
     * @description 根据流读取Excel文件
     * @date 2023/4/8 14:21
     */
    public List<ArrayList<String>> read(InputStream inputStream,
                                        boolean isExcel2003, int sheetNo, int beginRowNo, int endRowNo) {
        List<ArrayList<String>> dataLst = null;
        try {
            // 根据版本选择创建Workbook的方式
            Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
                    : new XSSFWorkbook(inputStream);
            dataLst = read(wb, sheetNo, beginRowNo, endRowNo);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataLst;
    }

    /**
     * @param :
     * @return int
     * @author aochong
     * @description 得到总行数
     * @date 2023/4/8 14:23
     */
    public int getTotalRows() {
        return totalRows;
    }

    /**
     * @param :
     * @return int
     * @author aochong
     * @description 得到总列数
     * @date 2023/4/8 14:23
     */
    public int getTotalCells() {
        return totalCells;
    }

    /**
     * @param wb: 工作簿
     * @param sheetNo: sheet页编号
     * @param beginRowNo: 开始行号
     * @param endRowNo: 结束行号
     * @return List<ArrayList<String>>
     * @author aochong
     * @description 读取数据
     * @date 2023/4/8 14:24
     */
    private List<ArrayList<String>> read(Workbook wb, int sheetNo,
                                         int beginRowNo, int endRowNo) {
        List<ArrayList<String>> dataLst = new ArrayList<ArrayList<String>>();

        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(sheetNo - 1);
        this.totalRows = sheet.getPhysicalNumberOfRows();
        if (this.totalRows >= 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        // 循环Excel的行
        // for (int r = 0; r < this.totalRows; r++) {
        for (int r = beginRowNo - 1; r < endRowNo; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            ArrayList<String> rowLst = new ArrayList<String>();
            // 循环Excel的列
            for (short c = 0; c < this.getTotalCells(); c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (cell == null) {
                    rowLst.add(cellValue);
                    continue;
                }
                // 处理数字型的,自动去零
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    // 在excel里,日期也是数字,在此要进行判断
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = DateUtil
                                .get4yMdHms(cell.getDateCellValue());
                    } else {
                        cellValue = getRightStr(cell.getNumericCellValue() + "");
                    }
                } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {   // 处理字符串型
                    cellValue = cell.getStringCellValue();
                } else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {  // 处理布尔型
                    cellValue = cell.getBooleanCellValue() + "";
                } else {    // 其它数据类型
                    cellValue = cell.toString() + "";
                }
                rowLst.add(cellValue);
            }
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    /**
     * @param sNum:
     * @return String
     * @author aochong
     * @description 正确地处理整数后自动加零的情况
     * @date 2023/4/8 14:25
     */
    private String getRightStr(String sNum) {
        DecimalFormat decimalFormat = new DecimalFormat("#.000000");
        String resultStr = decimalFormat.format(new Double(sNum));
        if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
            resultStr = resultStr.substring(0, resultStr.indexOf("."));
        }
        return resultStr;
    }

    /**
     * @param out:
     * @return void
     * @author aochong
     * @description 初始化write
     * @date 2023/4/8 14:25
     */
    public void initWrite(OutputStream out,boolean isExcel2003) {
        this.out = out;
        if (isExcel2003) {
            this.workbook = new HSSFWorkbook();
        }else {
            this.workbook = new XSSFWorkbook();
        }
        this.sheet = workbook.createSheet();
    }

    /**
     * @param :
     * @return void
     * @author aochong
     * @description 导出Excel文件
     * @date 2023/4/8 14:25
     */
    public void export() throws Exception {
        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (FileNotFoundException e) {
            throw new Exception("生成导出Excel文件出错! ", e);
        } catch (IOException e) {
            throw new Exception("写入Excel文件出错! ", e);
        }
    }

    /**
     * @param index:
     * @return void
     * @author aochong
     * @description 增加一行
     * @date 2023/4/8 14:26
     */
    public void createRow(int index) {
        this.row = this.sheet.createRow(index);
    }

    /**
     * @param index: 列号
     * @param value: 单元格填充值
     * @return void
     * @author aochong
     * @description 设置单元格
     * @date 2023/4/8 14:26
     */
    public void setCell(int index, int value) {
        Cell cell = this.row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
    }

    /**
     * @param index: 列号
     * @param value: 单元格填充值
     * @return void
     * @author aochong
     * @description 设置单元格
     * @date 2023/4/8 14:26
     */
    public void setCell(int index, double value) {
        Cell cell = this.row.createCell(index);
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        // 建立新的cell样式
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        // 设置cell样式为定制的浮点数格式
        cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
        // 设置该cell浮点数的显示格式
        cell.setCellStyle(cellStyle);
    }

    /**
     * @param index: 列号
     * @param value: 单元格填充值
     * @return void
     * @author aochong
     * @description 设置单元格
     * @date 2023/4/8 14:27
     */
    public void setCell(int index, String value) {
        Cell cell = this.row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(value);
    }

    /**
     * @param index: 列号
     * @param value: 单元格填充值
     * @return void
     * @author aochong
     * @description 设置单元格
     * @date 2023/4/8 14:27
     */
    public void setCell(int index, Calendar value) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value.getTime());
        // 建立新的cell样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置cell样式为定制的日期格式
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
        // 设置该cell日期的显示格式
        cell.setCellStyle(cellStyle);
    }

    /**
     * @param :
     * @return Sheet
     * @author aochong
     * @description 获取当前Excel对象Sheet页对象
     * @date 2023/4/8 14:39
     */
    public Sheet getSheet(){
        return sheet;
    }

    /**
     * @param :
     * @return Workbook
     * @author aochong
     * @description 获取当前Excel对象的WorkBook对象
     * @date 2023/4/8 17:45
     */
    public Workbook getWorkbook(){
        return workbook;
    }

    public static void main(String[] args) throws Exception {
        POIExcelUtils e = new POIExcelUtils();

        System.out.println(" 开始导出Excel文件 ");
        File f = new File("test.xlsx");
        try {
            e.initWrite(new FileOutputStream(f),false);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }

        e.createRow(0);
        e.setCell(0, "试题编码 ");
        e.setCell(1, "题型");
        e.setCell(2, "分值");
        e.setCell(3, "难度");
        e.setCell(4, "级别");
        e.setCell(5, "知识点");

        for (int i = 1; i < 10; i++) {
            e.createRow(i);
            e.setCell(0, "t" + i);
            e.setCell(1, 1 + i);
            e.setCell(2, 3.0 + i);
            e.setCell(3, 1 + i);
            e.setCell(4, "重要" + i);
            e.setCell(5, "专业" + i);
        }
        try {
            e.export();
            System.out.println(" 导出Excel文件[成功] ");
        } catch (Exception ex) {
            System.out.println(" 导出Excel文件[失败] ");
            ex.printStackTrace();
        }
    }
}
